package com.nari.sink.dao.impl;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Criteria;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;

import com.nari.coreframe.dao.hibernate3.GenericDaoImpl;
import com.nari.platform.model.PageCond;
import com.nari.platform.model.ResultWithPageCond;
import com.nari.rescontroller.dao.IResourceAccessControlDao;
import com.nari.sink.bo.PSinkInfo;
import com.nari.sink.dao.ISinkDao;

public class SinkDaoImpl extends GenericDaoImpl<PSinkInfo, String> implements ISinkDao {
	private IResourceAccessControlDao iResourceAccessControlDao;
	
	/**
	 * 分页查询散热器列表
	 * @param sinkNo 散热器编号(可选)
	 * @param projectUID 所属项目(可选)
	 * @param communityUID 所属小区(可选)
	 * @param buildingUID 所属楼栋(可选)
	 * @param roomUID 所属热用户(可选)
	 * @param pageCond
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2013-8-28
	 */
	@SuppressWarnings("unchecked")
	public ResultWithPageCond<PSinkInfo> queryHeatSinkPage(String sinkNo,
			String projectUID, String communityUID, String buildingUID, 
			String roomUID , PageCond pageCond) throws Exception {
		Criteria listCriteria = this.getCurrentSession().createCriteria(PSinkInfo.class);
		Criteria countCriteria = this.getCurrentSession().createCriteria(PSinkInfo.class);
		//散热器编号
		if (sinkNo != null && !"".equals(sinkNo.trim())) {
			listCriteria.add(Restrictions.ilike("sinkNo", sinkNo.trim().toLowerCase(), MatchMode.ANYWHERE));
			countCriteria.add(Restrictions.ilike("sinkNo", sinkNo.trim().toLowerCase(), MatchMode.ANYWHERE));
		}
		//所属项目
		if (projectUID!=null && !"".equals(projectUID.trim())) {
			listCriteria.add(Restrictions.eq("projectID", projectUID.trim()));
			countCriteria.add(Restrictions.eq("projectID", projectUID.trim()));
		}
		//所属小区
		if (communityUID!=null && !"".equals(communityUID.trim())) {
			listCriteria.add(Restrictions.eq("communityID", communityUID.trim()));
			countCriteria.add(Restrictions.eq("communityID", communityUID.trim()));
		}
		//所属楼栋
		if (buildingUID!=null && !"".equals(buildingUID.trim())) {
			listCriteria.add(Restrictions.eq("buildingID", buildingUID.trim()));
			countCriteria.add(Restrictions.eq("buildingID", buildingUID.trim()));
		}
		//所属热用户
		if (roomUID!=null && !"".equals(roomUID.trim())) {
			listCriteria.add(Restrictions.eq("roomId", roomUID.trim()));
			countCriteria.add(Restrictions.eq("roomId", roomUID.trim()));
		}
		//权限资源匹配
		String authorityRes = iResourceAccessControlDao.queryAuthorityProjResSql();
		if(!"".equals(authorityRes)) {
			//这是一个直接与底层数据库耦合的查询，注意避免数模更改带来的系统问题
			listCriteria.add(Restrictions.sqlRestriction("{alias}.t_project_id in("+authorityRes+")"));
			countCriteria.add(Restrictions.sqlRestriction("{alias}.t_project_id in("+authorityRes+")"));
		}
		//统计结果集个数
		int total = ((Long)countCriteria.setProjection(Projections.rowCount()).list().get(0)).intValue();
		//结果集排序
		listCriteria.addOrder(Order.asc("sinkNo"));
		//结果集分页
		listCriteria.setFirstResult(pageCond.getStart()).setMaxResults(pageCond.getLimit());
		//构造返回类型
		pageCond.setTotal(total);
		ResultWithPageCond<PSinkInfo> sinkerPage = new ResultWithPageCond<PSinkInfo>();
		sinkerPage.setList(listCriteria.list());
		sinkerPage.setPageCond(pageCond);
		return sinkerPage;
	}
	
	/**
	 * 通过所属关系（所属项目、所属小区、所属楼栋、所属热用户）查询散热器列表
	 * 若所属关系均不存在，则返回空列表
	 * @param projectUID 所属项目(可选)
	 * @param communityUID 所属小区(可选)
	 * @param buildingUID 所属楼栋(可选)
	 * @param roomUID 所属热用户(可选)
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2013-8-28
	 */
	@SuppressWarnings("unchecked")
	public List<PSinkInfo> querySinkerListByBelongs(String projectUID, String communityUID, 
			String buildingUID, String roomUID) throws Exception {
		if(projectUID!=null && !"".equals(projectUID.trim()) 
				|| (communityUID!=null && !"".equals(communityUID.trim())) 
				|| (buildingUID!=null && !"".equals(buildingUID.trim())) 
				|| (roomUID!=null && !"".equals(roomUID.trim()))) {
			Criteria listCriteria = this.getCurrentSession().createCriteria(PSinkInfo.class);
			//所属项目
			if (projectUID!=null && !"".equals(projectUID.trim())) {
				listCriteria.add(Restrictions.eq("projectID", projectUID.trim()));
			}
			//所属小区
			if (communityUID!=null && !"".equals(communityUID.trim())) {
				listCriteria.add(Restrictions.eq("communityID", communityUID.trim()));
			}
			//所属楼栋
			if (buildingUID!=null && !"".equals(buildingUID.trim())) {
				listCriteria.add(Restrictions.eq("buildingID", buildingUID.trim()));
			}
			//所属热用户
			if (roomUID!=null && !"".equals(roomUID.trim())) {
				listCriteria.add(Restrictions.eq("roomId", roomUID.trim()));
			}
			//权限资源匹配
			String authorityRes = iResourceAccessControlDao.queryAuthorityProjResSql();
			if(!"".equals(authorityRes)) {
				//这是一个直接与底层数据库耦合的查询，注意避免数模更改带来的系统问题
				listCriteria.add(Restrictions.sqlRestriction("{alias}.t_project_id in("+authorityRes+")"));
			}
			//结果集排序
			listCriteria.addOrder(Order.asc("sinkNo"));
			return listCriteria.list();
		}
		return new ArrayList<PSinkInfo>();
	}
	
	/**
	 * 通过散热器ID列表查询散热器ID-散热器号sinkNo映射表
	 * @param idList 散热器ID列表，若列表为空，则返回一个空映射表
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2013-8-29
	 */
	public Map<String, String> queryIdNameMapByIDList(List<String> idList) throws Exception {
		Map<String, String> idNameMap = new HashMap<String, String>();
		if(idList!=null && idList.size()>0) {
			StringBuffer hsql = new StringBuffer("select distinct sink.id,sink.sinkNo ");
			hsql.append("from PSinkInfo sink ");
			hsql.append("where sink.id in(");
			for(int index=0; index<idList.size(); index++) {
				hsql.append("'");
				hsql.append(idList.get(index));
				hsql.append("'");
				if(index < idList.size()-1) {
					hsql.append(",");
				}
			}
			hsql.append(")");
			List<Object> resultsList = this.runQueryHSqlObject(hsql.toString());
			//格式化查询结果集
			if(resultsList!=null && resultsList.size()>0) {
				for(Object result: resultsList) {
					Object[] arrayMap = (Object[]) result;
					idNameMap.put(arrayMap[0].toString(), arrayMap[1].toString());
				}
			}
		}
		return idNameMap;
	}
	
	/**
	 * 根据所属关系查询可绑定分配表的散热器列表
	 * 散热器与分配表的绑定原则是一一对应
	 * @param projectUID 所属项目(可选)
	 * @param communityUID 所属小区(可选)
	 * @param buildingUID 所属楼栋(可选)
	 * @param roomUID 所属热用户(可选)
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2013-8-29
	 */
	public List<PSinkInfo> queryBindableSinkerByBelongs(String projectUID, String communityUID, 
			String buildingUID, String roomUID) throws Exception {
		//1、查询不可绑定的散热器ID(该散热器已被其他分配表绑定)
		StringBuffer nonBindableQuery = new StringBuffer("select am.heatSinkID ");
		nonBindableQuery.append("from IAssignMeter am ");
		nonBindableQuery.append("where ");
		if(projectUID!=null && !"".equals(projectUID.trim()) 
				|| (communityUID!=null && !"".equals(communityUID.trim())) 
				|| (buildingUID!=null && !"".equals(buildingUID.trim())) 
				|| (roomUID!=null && !"".equals(roomUID.trim()))) {
			//所属项目
			if (projectUID!=null && !"".equals(projectUID.trim())) {
				nonBindableQuery.append("am.projectID='");
				nonBindableQuery.append(projectUID.trim());
				nonBindableQuery.append("' ");
				nonBindableQuery.append("and ");
			}
			//所属小区
			if (communityUID!=null && !"".equals(communityUID.trim())) {
				nonBindableQuery.append("am.communityID='");
				nonBindableQuery.append(communityUID.trim());
				nonBindableQuery.append("' ");
				nonBindableQuery.append("and ");
			}
			//所属楼栋
			if (buildingUID!=null && !"".equals(buildingUID.trim())) {
				nonBindableQuery.append("am.buildingID='");
				nonBindableQuery.append(buildingUID.trim());
				nonBindableQuery.append("' ");
				nonBindableQuery.append("and ");
			}
			//所属热用户
			if (roomUID!=null && !"".equals(roomUID.trim())) {
				nonBindableQuery.append("am.roomId='");
				nonBindableQuery.append(roomUID.trim());
				nonBindableQuery.append("' ");
				nonBindableQuery.append("and ");
			}
		}
		nonBindableQuery.append("am.heatSinkID is not null and am.heatSinkID<>''");
		//2、根据所属关系查询散热器
		StringBuffer sinkerQuery = new StringBuffer("from PSinkInfo sink ");
		sinkerQuery.append("where ");
		if(projectUID!=null && !"".equals(projectUID.trim()) 
				|| (communityUID!=null && !"".equals(communityUID.trim())) 
				|| (buildingUID!=null && !"".equals(buildingUID.trim())) 
				|| (roomUID!=null && !"".equals(roomUID.trim()))) {
			//所属项目
			if (projectUID!=null && !"".equals(projectUID.trim())) {
				sinkerQuery.append("sink.projectID='");
				sinkerQuery.append(projectUID.trim());
				sinkerQuery.append("' ");
				sinkerQuery.append("and ");
			}
			//所属小区
			if (communityUID!=null && !"".equals(communityUID.trim())) {
				sinkerQuery.append("sink.communityID='");
				sinkerQuery.append(communityUID.trim());
				sinkerQuery.append("' ");
				sinkerQuery.append("and ");
			}
			//所属楼栋
			if (buildingUID!=null && !"".equals(buildingUID.trim())) {
				sinkerQuery.append("sink.buildingID='");
				sinkerQuery.append(buildingUID.trim());
				sinkerQuery.append("' ");
				sinkerQuery.append("and ");
			}
			//所属热用户
			if (roomUID!=null && !"".equals(roomUID.trim())) {
				sinkerQuery.append("sink.roomId='");
				sinkerQuery.append(roomUID.trim());
				sinkerQuery.append("' ");
				sinkerQuery.append("and ");
			}
		}
		//3、排除不可绑定的散热器
		sinkerQuery.append("sink.id not in(");
		sinkerQuery.append(nonBindableQuery.toString());
		sinkerQuery.append(") ");
		//4、散热器列表排序
		sinkerQuery.append("order by sink.sinkNo asc");
		return this.runQueryHSql(sinkerQuery.toString());
	}
	
	/**
	 * 根据所属热用户信息查询散热器列表
	 * @param sinkNo 散热器编号(可选)
	 * @param projectUID 所属项目(可选)
	 * @param communityUID 所属小区(可选)
	 * @param buildingUID 所属楼栋(可选)
	 * @param heatRoomUID 所属热用户(必填)
	 * 说明：若热用户ID不存在，则返回零结果集
	 * @param pageCond
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2013-9-4
	 */
	@SuppressWarnings("unchecked")
	public ResultWithPageCond<PSinkInfo> querySinkerPageByRoomUID(String sinkNo,
			String projectUID, String communityUID, String buildingUID, 
			String heatRoomUID , PageCond pageCond) throws Exception {
		List<PSinkInfo> sinkerList = new ArrayList<PSinkInfo>();
		int total = 0;
		//热用户存在时，分页查询散热器列表
		if(heatRoomUID!=null && !"".equals(heatRoomUID.trim())) {
			Criteria listCriteria = this.getCurrentSession().createCriteria(PSinkInfo.class);
			Criteria countCriteria = this.getCurrentSession().createCriteria(PSinkInfo.class);
			//所属热用户
			listCriteria.add(Restrictions.eq("roomId", heatRoomUID.trim()));
			countCriteria.add(Restrictions.eq("roomId", heatRoomUID.trim()));
			//散热器编号
			if (sinkNo != null && !"".equals(sinkNo.trim())) {
				listCriteria.add(Restrictions.ilike("sinkNo", sinkNo.trim().toLowerCase(), MatchMode.ANYWHERE));
				countCriteria.add(Restrictions.ilike("sinkNo", sinkNo.trim().toLowerCase(), MatchMode.ANYWHERE));
			}
			//所属项目
			if (projectUID!=null && !"".equals(projectUID.trim())) {
				listCriteria.add(Restrictions.eq("projectID", projectUID.trim()));
				countCriteria.add(Restrictions.eq("projectID", projectUID.trim()));
			}
			//所属小区
			if (communityUID!=null && !"".equals(communityUID.trim())) {
				listCriteria.add(Restrictions.eq("communityID", communityUID.trim()));
				countCriteria.add(Restrictions.eq("communityID", communityUID.trim()));
			}
			//所属楼栋
			if (buildingUID!=null && !"".equals(buildingUID.trim())) {
				listCriteria.add(Restrictions.eq("buildingID", buildingUID.trim()));
				countCriteria.add(Restrictions.eq("buildingID", buildingUID.trim()));
			}
			//统计结果集个数
			total = ((Long)countCriteria.setProjection(Projections.rowCount()).list().get(0)).intValue();
			//结果集排序
			listCriteria.addOrder(Order.asc("sinkNo"));
			//结果集分页
			listCriteria.setFirstResult(pageCond.getStart()).setMaxResults(pageCond.getLimit());
			sinkerList = listCriteria.list();
		}
		//构造返回类型
		pageCond.setTotal(total);
		ResultWithPageCond<PSinkInfo> sinkerPage = new ResultWithPageCond<PSinkInfo>();
		sinkerPage.setList(sinkerList);
		sinkerPage.setPageCond(pageCond);
		return sinkerPage;
	}
	
	/**
	 * 根据散热器唯一标识计算该散热器的评价系数
	 * 散热器评价系数计算公式：
	 * 评价系数K = Kq(功率修正系数) * Kc(耦合修正系数) * Kt(低温修正系数)
	 * 其中功率修正系数Kq = 散热器单片功率 * 散热器片数
	 * @param sinkUID
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2013-10-4
	 */
	public double calcSinkEvaluateFactor(String sinkUID) throws Exception {
		PSinkInfo heatSinker = this.queryEntityById(sinkUID);
		double kFactor = new BigDecimal(heatSinker.getKqFactor())
				.multiply(new BigDecimal(heatSinker.getKtFactor()))
				.multiply(new BigDecimal(heatSinker.getKcFactor()))
				.doubleValue();
		return kFactor;
	}
	
	/**
	 * 通过分摊域唯一标识统计散热器的个数
	 * @param shareAreaUID 分摊域唯一标识(必填)
	 * @param ifMeasureCharge 是否参与计量收费
	 * 说明：1:不参与		2:参与
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2013-10-4
	 */
	public long countSinkerNumByAreaUID(String shareAreaUID, 
			Integer ifMeasureCharge) throws Exception {
		//查询热用户
		String roomQuery = "select hr.id from IHeatRoom hr " +
				"where hr.shareAreaID='"+shareAreaUID+"'";
		if(ifMeasureCharge != null) {
			roomQuery += " and hr.ifMeasureCharge="+ifMeasureCharge;
		}
		String hsql = "select count(*) from PSinkInfo sink " +
				"where sink.roomId in(" + roomQuery+ ")";
		return (Long)this.getCurrentSession().createQuery(hsql).uniqueResult();
	}
	
	/**
	 * 通过热用户唯一标识统计散热器的个数
	 * @param heatRoomUID
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2013-10-4
	 */
	public long countSinkerNumByRoomUID(String heatRoomUID) throws Exception {
		String hsql = "select count(*) from PSinkInfo sink " +
				"where sink.roomId='"+heatRoomUID+"'";
		return (Long)this.getCurrentSession().createQuery(hsql).uniqueResult();
	}
	
	/**
	 * 通过所属关系（所属项目、所属小区、所属楼栋、所属热用户）统计散热器的个数
	 * 若所属关系均不存在，则返回空列表
	 * @param projectUID 所属项目(可选)
	 * @param communityUID 所属小区(可选)
	 * @param buildingUID 所属楼栋(可选)
	 * @param roomUID 所属热用户(可选)
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2013-10-23
	 */
	public int countSinkerSumByBelongs(String projectUID, String communityUID, 
			String buildingUID, String roomUID) throws Exception {
		Criteria countCriteria = this.getCurrentSession().createCriteria(PSinkInfo.class);
		//所属项目
		if (projectUID!=null && !"".equals(projectUID.trim())) {
			countCriteria.add(Restrictions.eq("projectID", projectUID.trim()));
		}
		//所属小区
		if (communityUID!=null && !"".equals(communityUID.trim())) {
			countCriteria.add(Restrictions.eq("communityID", communityUID.trim()));
		}
		//所属楼栋
		if (buildingUID!=null && !"".equals(buildingUID.trim())) {
			countCriteria.add(Restrictions.eq("buildingID", buildingUID.trim()));
		}
		//所属热用户
		if (roomUID!=null && !"".equals(roomUID.trim())) {
			countCriteria.add(Restrictions.eq("roomId", roomUID.trim()));
		}
		//权限资源匹配
		String authorityRes = iResourceAccessControlDao.queryAuthorityProjResSql();
		if(!"".equals(authorityRes)) {
			//这是一个直接与底层数据库耦合的查询，注意避免数模更改带来的系统问题
			countCriteria.add(Restrictions.sqlRestriction("{alias}.t_project_id in("+authorityRes+")"));
		}
		//统计结果集个数
		return ((Long)countCriteria.setProjection(Projections.rowCount()).list().get(0)).intValue();
	}
	
	/**
	 * 通过散热器编号查询散热器信息
	 * @param sinkNo
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2014-1-12
	 */
	public PSinkInfo querySinkerBySinkNo(String sinkNo) throws Exception {
		String hsql = "from PSinkInfo sink where sink.sinkNo='"+sinkNo+"'";
		List<PSinkInfo> sinkerList = this.runQueryHSql(hsql);
		//散热器信息
		PSinkInfo sinker = null;
		if(sinkerList!=null && sinkerList.size()>0) {
			sinker = sinkerList.get(0);
		}
		return sinker;
	}
	
	/**
	 * 通过散热器编号查询隶属于某一热用户的散热器
	 * @param sinkNo
	 * @param roomUID
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2014-1-22
	 */
	public PSinkInfo querySinkerBySinkNo(String sinkNo, String roomUID) throws Exception {
		String hsql = "from PSinkInfo sink where sink.sinkNo='"+sinkNo+"' " +
				"and sink.roomId='"+roomUID+"'";
		List<PSinkInfo> sinkerList = this.runQueryHSql(hsql);
		//散热器信息
		PSinkInfo sinker = null;
		if(sinkerList!=null && sinkerList.size()>0) {
			sinker = sinkerList.get(0);
		}
		return sinker;
	}
	
	/**
	 * 统计相同散热器编号的数量，避免系统录入相同散热器编号的散热器信息
	 * @param sinkNo
	 * @param sinkerUID
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2014-7-28
	 */
	public int countBySinkNo(String sinkNo, String sinkerUID) throws Exception {
		String hsql = "select count(*) from PSinkInfo sink " +
				"where sink.sinkNo='"+sinkNo+"'";
		if(sinkerUID!=null && !"".equals(sinkerUID)) {
			hsql += " and sink.id<>'"+sinkerUID+"'";
		}
		return ((Long)this.getCurrentSession().createQuery(hsql).uniqueResult()).intValue();
	}

	/**
	 * @return the iResourceAccessControlDao
	 */
	public IResourceAccessControlDao getiResourceAccessControlDao() {
		return iResourceAccessControlDao;
	}

	/**
	 * @param iResourceAccessControlDao the iResourceAccessControlDao to set
	 */
	public void setiResourceAccessControlDao(
			IResourceAccessControlDao iResourceAccessControlDao) {
		this.iResourceAccessControlDao = iResourceAccessControlDao;
	}
	
}
